package com.elite;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.math.BigDecimal;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPClientConfig;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.elite.algorithm.GUID;

public class FileFTP implements Runnable{
	private static final Log log=LogFactory.getLog(FileFTP.class);
	public static com.elite.FtpBean ftpbean;
	public static String ip;
	public static String port;
	public static String username;
	public static String password;
	public static String dotime;
	public static String filename;
	public static String sqlname;
	public static String sqlname1;
	public static String result;
	private static String filename1="";
	private static FTPFile[] ff=null;
	private static String sql="";
	private static List sql_list=null;
	private static String name1="";
	private static String name2="";
	private static String judgement="";
	private boolean flag = true; //停止线程标记
	private int judgeNum=0;
	private static String url="";
	private static String urljudge="";
	public static String path="";
	public static String getPath() {
		return path;
	}

	public static void setPath(String path) {
		FileFTP.path = path;
	}

	public com.elite.FtpBean getFtpbean() {
		return ftpbean;
	}

	public void setFtpbean(com.elite.FtpBean ftpbean) {
		this.ftpbean = ftpbean;
	}
	
	public String getIp() {
		return ip;
	}

	public void setIp(String ip) {
		this.ip = ip;
	}

	public String getPort() {
		return port;
	}

	public void setPort(String port) {
		this.port = port;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDotime() {
		return dotime;
	}

	public void setDotime(String dotime) {
		this.dotime = dotime;
	}

	public String getFilename() {
		return filename;
	}

	public void setFilename(String filename) {
		this.filename = filename;
	}

	public static String getSqlname() {
		return sqlname;
	}

	public static void setSqlname(String sqlname) {
		FileFTP.sqlname = sqlname;
	}

	public static String getResult() {
		return result;
	}

	public static void setResult(String result) {
		FileFTP.result = result;
	}

	public static String getSqlname1() {
		return sqlname1;
	}

	public static void setSqlname1(String sqlname1) {
		FileFTP.sqlname1 = sqlname1;
	}
	public static void CreateFile(String path){
		try{
			File dirFile  =   new  File(path);
             if ( ! (dirFile.exists())  &&   ! (dirFile.isDirectory())) {
                 boolean  creadok  =  dirFile.mkdirs();
                 if (creadok) {
                	 log.info( " ok:创建文件夹成功！ " );
                } else {
                	log.info( " err:创建文件夹失败！ " );                    
                } 
		}	
	}catch(Exception e){
		log.info("创建文件夹失败:"+getERRO(e));
	}
	}
	
	public static void changeDirectory(String oldpath,String newpath,boolean cover){   
	    if(!oldpath.equals(newpath)){   
	        File oldfile=new File(oldpath);   
	        File newfile=new File(newpath);   
	        if(newfile.exists()){//若在待转移目录下，已经存在待转移文件   
	            if(cover){
	            	oldfile.renameTo(newfile); //覆盖   
	            }else{
	            	log.info("在新目录下已经存在："+filename); 
	            }
	        }else{   
	        	if(copyFile(oldpath,newpath)==true){
	        		oldfile.delete();
	        	}  
	        }   
	    }          
	}
	
	   public  static boolean copyFile(String  oldPath,  String  newPath)  {     
	       try  {     
	           int  bytesum  =  0;     
	           int  byteread  =  0;     
	           File  oldfile  =  new  File(oldPath);     
	           if  (oldfile.exists())  {  //文件存在时     
	               InputStream  inStream  =  new  FileInputStream(oldPath);  //读入原文件     
	               FileOutputStream  fs  =  new  FileOutputStream(newPath);     
	               byte[]  buffer  =  new  byte[1444];     
	               int  length;     
	               while  (  (byteread  =  inStream.read(buffer))  !=  -1)  {     
	                   bytesum  +=  byteread;  //字节数  文件大小       
	                   fs.write(buffer,  0,  byteread);     
	               }     
	               inStream.close();     
	           }   
	           return true;
	       }     
	       catch  (Exception  e)  {  
	           log.info("复制单个文件操作出错:"+getERRO(e));        
	           return false;
	       }     
	    
	   } 
	 public static String getERRO(Exception e){
			String str="";
			try{
	            StringWriter sw = new StringWriter();  
	            e.printStackTrace(new PrintWriter(sw, true));  
	            str = sw.toString(); 	
			}catch(Exception e1){
				str=e.toString();
				log.error("getERRO:"+getERRO(e1));
			}
			return str;
		}
	public void run(){
		while(flag){
			url="ftp://"+ip+":"+port+"/";
			Date date=new Date();
			String nowtime=String.format("%tR",date); //获取现在几点
			nowtime=nowtime.substring(0,2);
			if(nowtime.equals(dotime)&&judgeNum==0){
				try{
					log.info("------doFTP()------");
					doFTP();
					judgeNum=0;
				}catch(Exception e){
					log.info("过程错误:"+getERRO(e));
				}
			}else if(nowtime.equals("24")){
				if(judgeNum==1){
					judgeNum=0;
				}else if(judgeNum==0){
					try{
						doFTP();
					}catch(Exception e){
						log.info("过程错误:"+getERRO(e));
					}
				}
			}
			try { 
				Thread.sleep(5*1000); 
				} 
				catch (Exception e) {
					log.info("错误:"+getERRO(e));
					} 
		}
	}
	public static FTPClient GetFtp(String host,int port,String username,String password){
		FTPClient ftp;
		ftp = new FTPClient(); 
		try{
			ftp.connect(host, port); 
			ftp.setControlEncoding("GBK");
			FTPClientConfig conf = new FTPClientConfig(FTPClientConfig.SYST_UNIX);
			conf.setServerLanguageCode("zh");			
			ftp.login(username, password);
		}catch(Exception e){
			log.info("FTP连接失败:"+getERRO(e));
		}
		return ftp;
		
	}
	
	public void doFTP() throws IOException{
		String file_name=filename;
		FTPClient ftp=GetFtp(ip,Integer.parseInt(port),username,password);
		ftp.enterLocalPassiveMode();
		if(ftp!=null){
				Date date1=new Date();
				String dd1=String.format("%tF",date1); //获取现在几号
				dd1=dd1.replace("-", "");
				if(file_name.equals("")){
					file_name=file_name+dd1;
				}else{
					file_name=file_name+"/"+dd1;
				}
               
				try{				
				ftp.changeWorkingDirectory("/callcenter/file");
//				ftp.changeWorkingDirectory("/callcenter/file/"+file_name);

				
				FTPFile[] ff1=ftp.listFiles();
				log.info("ff1.length:"+ff1.length);
				for(int i=0;i<ff1.length;i++){
					log.info("i:"+ff1[i].getName());					
					String name=ff1[i].getName();
					log.info("name:"+name);
					if(name.equals("20120229")){
						log.info("1:"+name);
						log.info("link:"+ff1[i].getLink());
					}
				}
				
				
				
				log.info("进入file_name:"+file_name);
				ff=ftp.listFiles(file_name);
//				ff=ftp.listFiles("UNIX",file_name);
//				ff=ftp.listFiles();
				}catch(Exception e){
					log.info("ftp.listFiles的错误:"+getERRO(e));
				}
				try{
				if(ff!=null&&ff.length>0){
					for(int i=0;i<ff.length;i++){
						filename1=ff[i].getName();
						filename1=new String(filename1.getBytes("gbk"), "iso8859-1");
                        log.info("path:"+path);
						log.info(" select * from XF_hflog where flag=1 and  descname='"+filename1+"' ");
			            List list11=ftpbean.chaxun(" select * from XF_hflog where flag=1 and  descname='"+filename1+"' ");
			            String judge_flag1="fail";
			            if(list11!=null&&list11.size()>0){
			            	Map map=(Map)list11.get(0);
			            	String descname=(String)map.get("descname");
			            	if(descname!=null&&descname.equals(filename1)){
			            		judge_flag1="sucess";
			            	}
			            }						
						String aaaq=filename1.substring(3,6);
						if(!judge_flag1.equals("sucess")&&aaaq.equals("XQY")){			            
						name1=new String(filename1.getBytes("iso8859-1"), "gbk");
						name1=name1.substring(0, name1.lastIndexOf("."));
						name2=path+"\\"+new String(filename1.getBytes("iso8859-1"), "gbk");
						File file = new File(name2);
						FileOutputStream fos = new FileOutputStream(file);
						try{ 
							 boolean flag=ftp.retrieveFile(file_name+"/" + filename1, fos);
						}catch(Throwable e){
							log.info("ftp.retrieveFile:"+e);
						}

						fos.close();
						String[] str={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE"};
						
						FileInputStream is = new FileInputStream(new File(name2)); 
				        

						ByteArrayOutputStream byteOS = new ByteArrayOutputStream(); 
						byte[] by = new byte[512];  
						int t = is.read(by,0,by.length); 
					    while(t>0){  
							 byteOS.write(by, 0, 512);  //这里别写成t，写够512，呵呵，SB的方法对付SB的java API
							 t = is.read(by,0,by.length);  
							} 
						byteOS.close();  
						InputStream byteIS = new ByteArrayInputStream(byteOS.toByteArray());  
				        HSSFWorkbook wb=new HSSFWorkbook(byteIS);
				        HSSFSheet childSheet = wb.getSheetAt(0);   
				        int rowNum=childSheet.getLastRowNum()+1;
				        int size=rowNum-1;
				        String[][] data=new String[size][60];
				        SimpleDateFormat DateFormat=new SimpleDateFormat("yyyy-MM-dd"); 
			            for(int j=1;j<rowNum;j++){   
			                HSSFRow row = childSheet.getRow(j);    
			                int cellNum=row.getLastCellNum(); 
			                for(int k=0;k<cellNum;k++)   
			                {   
			                	HSSFCell cell=row.getCell((short) k);
			                	try{ 
			            		if(cell==null){
			            			data[j-1][k]="";
			            		}else if(cell.getCellType()==3){
			                		data[j-1][k]="";
			                	}else if(cell.getCellType()==1){
			                    		String a=cell.toString();
			                			data[j-1][k]=cell.toString();
			                	}else if(cell.getCellType()==0){
			                		if(HSSFDateUtil.isCellDateFormatted(cell)||k==16||k==29||k==34||k==35||k==36||k==37||k==45||k==46)
			                        {
			                        	data[j-1][k]= DateFormat.format(cell.getDateCellValue());
			                        	if(data[j-1][k].length()>19){
			                        	  data[j-1][k]= DateFormat.format(cell.getDateCellValue()).substring(0, 19);
			                        	}
			                        }else{
			                        		double a=cell.getNumericCellValue();
			                        		data[j-1][k]=a+"";
			                        }
			                	}else if(HSSFCell.CELL_TYPE_BLANK == cell.getCellType()){
			                		data[j-1][k]="";
			                	}
			                	 }catch(Exception e){
			                		 log.error("cell.getCellType():"+cell.getCellType());
			                		 log.error("cell:"+cell);
			                		 log.error(getERRO(e));
			                	 }
			                }     
			            }  
			            
			            int aaa=0;
			            
			            String[] s;  
			            s=new String[data.length];			         
			            for(int i1=0;i1<data.length;i1++){
			            	String MANAGECOM=data[i1][2];  //管理机构C
			            	String MANAGECOMID=data[i1][3];   //机构代码D
			            	String MANAGECOMNAME=data[i1][4];    //机构名称E
			            	String SALECHNL=data[i1][5];              //销售渠道F
			            	String YBTFLAG=data[i1][6];   //银保通标识G
			            	String AGENTCOMCODE=data[i1][7];  // 代理机构代码H
			            	String AGENTCOMNAME=data[i1][8]; // 代理机构I
			            	String CONTNO=data[i1][10];   //保单号码K
			            	String CONTSTATE=data[i1][11]; //保单状态L
			            	String APPNTNAME=data[i1][13];   // 投保人姓名N
			            	String APPNTNO=data[i1][14];    //投保客户号O
			            	String APPNTSEX=data[i1][15];        //投保人性别P
			            	String APPNTBIRTHDAY=data[i1][16];   //出生日期-投保人信息Q
			            	if(APPNTBIRTHDAY!=null&&APPNTBIRTHDAY.length()>3){
			            		APPNTBIRTHDAY="to_date('"+APPNTBIRTHDAY+"','yyyy-mm-dd')";
			            	}else{
			            		APPNTBIRTHDAY="''";
			            	}
			            	String APPNTAGE=data[i1][17];   //投保人年龄R
			            	String APPNTIDTYPE=data[i1][18];    //证件类型S
			            	String APPNTIDNO=data[i1][19];     //证件号码T 111
			            	String APPNTMOBILE=data[i1][20];    //移动电话U
			            	String APPNTPHONE=data[i1][21];    //固定电话V
			            	String APPNTPHONE1=data[i1][22];    //固定电话W        
			            	String mail=data[i1][23];    //幽香X
			            	String APPNTADDRESS=data[i1][24];   //联系地址Y
			            	String APPNTZIPCODE=data[i1][25];   //邮政编码Z
			            	String INUSERDNAME=data[i1][26];    // 被保人姓名AA
			            	String INSUREDNO=data[i1][27];    //被保客户号AB
			            	String INSUREDSET=data[i1][28];   //被保人性别AC
			            	String INSUREDBIRTHDAY=data[i1][29];   //出生日期AD
			            	if(INSUREDBIRTHDAY!=null&&INSUREDBIRTHDAY.length()>3){
			            		INSUREDBIRTHDAY="to_date('"+INSUREDBIRTHDAY+"','yyyy-mm-dd')";
			            	}else{
			            		INSUREDBIRTHDAY="''";
			            	}
			              	String INSUERDAGE=data[i1][30];   //被保人年龄AE
			            	String INSUREDIDTYPE=data[i1][31];   //被保人证件类型AF
			            	String INSUERDIDNO=data[i1][32];   //被保人证件号码AG
			            	String RELATIONTOAPPNT=data[i1][33];   //与投保人关系AH
			            	String SIGEDATE=data[i1][34];   //签单日期AI
			            	if(SIGEDATE!=null&&SIGEDATE.length()>3){
			            		SIGEDATE="to_date('"+SIGEDATE+"','yyyy-mm-dd')";
			            	}else{
			            		SIGEDATE="''";
			            	}
			            	String CVALIDATE=data[i1][35];   //生效日期AJ
			            	if(CVALIDATE!=null&&CVALIDATE.length()>3){
			            		CVALIDATE="to_date('"+CVALIDATE+"','yyyy-mm-dd')";
			            	}else{
			            		CVALIDATE="''";
			            	}
			            	String CUSTOMGETPOLDATE=data[i1][36];   //客户签收日期AK
			            	if(CUSTOMGETPOLDATE!=null&&CUSTOMGETPOLDATE.length()>3){
			            		CUSTOMGETPOLDATE="to_date('"+CUSTOMGETPOLDATE+"','yyyy-mm-dd')";
			            	}else{
			            		CUSTOMGETPOLDATE="''";
			            	}
			            	String GETPOLDATE=data[i1][37];   //回单日期AL
			            	if(GETPOLDATE!=null&&GETPOLDATE.length()>3){
			            		GETPOLDATE="to_date('"+GETPOLDATE+"','yyyy-mm-dd')";
			            	}else{
			            		GETPOLDATE="''";
			            	}
			            	String RISKCODE=data[i1][38];   //险种编码AM
			            	String RISKNAME=data[i1][39];   //险种名称AN
			            	String AGENTGROUP=data[i1][51];   //代理人部组号AZ
			            	String BRAANCHCODE=data[i1][52];   //展业机构code BA
			            	String BRAANCHTYPE=data[i1][53];   //展业机构BB
			            	String AGENTCODE=data[i1][54];   //代理人代码BC
			            	String AGENTNAME=data[i1][55];   //代理人姓名BD
			            	String AGENTMOBILE=data[i1][56];   //移动电话BE
			            	String cddate=data[i1][1];   //抽档日期-创建时间-----------------
			            	if(cddate!=null&&cddate.length()>3){
			            		cddate="to_date('"+cddate+"','yyyy-mm-dd')";
			            	}else{
			            		cddate="''";
			            	}
			            	String YWTYPE=data[i1][9];   //业务类型J
			            	String YIPFLAG=data[i1][12];   //VIP标识M
			            	String BFTOTAL=data[i1][40];   //保费合计AO
			            	String ZXPREMIUM=data[i1][41];   //主险保费AP
			            	String ZXINSURED=data[i1][42];   //主险保额AQ
			            	String FJXPREMIUM=data[i1][43];   //附加险保费总额AR
			            	String GRADE=data[i1][44];   //档次AS
			            	String BXZQDATE=data[i1][45];   //保险止期AT
			            	if(BXZQDATE!=null&&BXZQDATE.length()>3){
			            		BXZQDATE="to_date('"+BXZQDATE+"','yyyy-mm-dd')";
			            	}else{
			            		BXZQDATE="''";
			            	}
			            	String PAYPERIOD=data[i1][46];   //交费年期AU
			            	String PAYTYPE=data[i1][47];   //交费形式AV
			            	String SQJFXS=data[i1][48];   //首期交费形式AW
			            	String XQJFXS=data[i1][49];   //续期交费形式AX
			            	String BBAGE=data[i1][50];   //保至被保险人年龄AY
			            	String CREATEBY="001";   //创建人----------------------------
			            	String OBJECTVIE_GUID="";   //OBJECTVIE_GUID---------------------
			            	String FLAG="1";   //是否生成外呼任务，1是生成0是未生成-------------------
			            	
			            	int judge1=0;
			            	String sql11="";
			            	
		                	sql11="insert into XF_VISIT(" +
	            			"MANAGECOM,MANAGECOMID,MANAGECOMNAME,SALECHNL,YBTFLAG,AGENTCOMNAME," +
	            			"CONTNO,CONTSTATE,APPNTNAME,APPNTNO,APPNTSEX,APPNTBIRTHDAY," +
	            			"APPNTAGE,APPNTIDTYPE,APPNTIDNO,APPNTMOBILE,APPNTPHONE,APPNTADDRESS," +
	            			"APPNTZIPCODE,INUSERDNAME,INSUREDNO,INSUREDSET,INSUREDBIRTHDAY,INSUERDAGE," +
	            			"INSUREDIDTYPE,INSUERDIDNO,RELATIONTOAPPNT,SIGEDATE, CVALIDATE,CUSTOMGETPOLDATE," +
	            			"GETPOLDATE,RISKCODE,RISKNAME,AGENTGROUP,BRAANCHTYPE,AGENTCODE," +
	            			"AGENTNAME,AGENTMOBILE,cddate,YWTYPE,YIPFLAG,BFTOTAL," +
	            			"ZXPREMIUM,ZXINSURED,FJXPREMIUM,GRADE,BXZQDATE,PAYPERIOD," +
	            			"PAYTYPE,SQJFXS,XQJFXS,BBAGE,CREATEBY,OBJECTVIE_GUID,FLAG,CREATEDATE,BRAANCHCODE,AGENTCOMCODE,MAIL,APPNTPHONE1) Values" +
	            			"('"+MANAGECOM+"','"+MANAGECOMID+"','"+MANAGECOMNAME+"','"+SALECHNL+"','"+YBTFLAG+"','"+AGENTCOMNAME+
	            			"','"+CONTNO+"','"+CONTSTATE+"','"+APPNTNAME+"','"+APPNTNO+"','"+APPNTSEX+"',"+APPNTBIRTHDAY+
	            			",'"+APPNTAGE+"','"+APPNTIDTYPE+"','"+APPNTIDNO+"','"+APPNTMOBILE+"','"+APPNTPHONE+"','"+APPNTADDRESS+
	            			"','"+APPNTZIPCODE+"','"+INUSERDNAME+"','"+INSUREDNO+"'," +"'"+INSUREDSET+"',"+INSUREDBIRTHDAY+",'"+INSUERDAGE+
	            			"','"+INSUREDIDTYPE+"','"+INSUERDIDNO+"','"+RELATIONTOAPPNT+"'," +SIGEDATE+","+CVALIDATE+","+CUSTOMGETPOLDATE+
	            			","+GETPOLDATE+",'"+RISKCODE+"','"+RISKNAME+"','"+AGENTGROUP+"','"+BRAANCHTYPE+"','"+AGENTCODE+
	            			"','"+AGENTNAME+"','"+AGENTMOBILE+"',"+cddate+",'"+YWTYPE+"','"+YIPFLAG+"','"+BFTOTAL+
	            			"','"+ZXPREMIUM+"','"+ZXINSURED+"','"+FJXPREMIUM+"','"+GRADE+"',"+BXZQDATE+",'"+PAYPERIOD+
	            			"','"+PAYTYPE+"','"+SQJFXS+"','"+XQJFXS+"','"+BBAGE+"','"+CREATEBY+"','"+OBJECTVIE_GUID+"',"+0+",sysdate,'"+BRAANCHCODE+"','"+AGENTCOMCODE+"','"+mail+"','"+APPNTPHONE1+"')";
			                s[i1]=sql11;   
			            }			            			            
						Date date=new Date();
						String dd=String.format("%tF",date); //获取现在几号
						dd=dd.replace("-", "");							
			            String guid=new GUID().toString();
			            String url=path+"\\"+dd+"\\"+new String(filename1.getBytes("iso8859-1"), "gbk");
			            String sql123=" insert into XF_hflog(descname,URL,flag,rqname,GUID)Values('"+filename1+"','"+url+"',1,'"+name2+"','"+guid+"')";
			            ftpbean.setSql123(sql123);
			            ftpbean.setA(s);
			            ftpbean.insertUser1();

			            List list1=ftpbean.chaxun(" select * from XF_hflog where GUID='"+guid+"'  ");
			            String judge_flag="fail";
			            if(list1!=null&&list1.size()>0){
			            	Map map=(Map)list1.get(0);
			            	String GUID=(String)map.get("GUID");
			            	if(GUID!=null&&GUID.equals(guid)){
			            		judge_flag="sucess";
			            	}
			            }
			            
			            if(judge_flag.equals("sucess")){
			            	CreateFile(path+"\\"+dd);
			            	changeDirectory(name2,path+"\\"+dd+"\\"+new String(filename1.getBytes("iso8859-1"), "gbk"),true);
			            	judgeNum=1;
			            }else{
			            	 String sql1234=" insert into XF_hflog(descname,flag,GUID)Values('"+filename1+"',2,'"+guid+"')";
			            	 log.info("sql1234:"+sql1234);
			            	 ftpbean.update(sql1234);
			            }			         
						}			           
					}
				}else{ 
					ftp.logout();
					ftp.disconnect();
					log.info("没有获得"+file_name+"路径下文件目录");
				}
			}catch(Exception e){
				ftp.logout();
				ftp.disconnect();
				log.info("扫描"+file_name+"路径下文件夹错误:"+getERRO(e));
			}
		
		}else{
			ftp.logout();
			ftp.disconnect();
			log.info("没有获得FTP连接对象");
		}	
		ftp.logout();
		ftp.disconnect();
	}
}





















